--    Program: 		CAPARIMP_AUTO_ACCOUNTING.sql
--    Description:  Import AR Auto Accountingn information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count_1		number;
   l_processed_count_2		number;
   l_rejected_count_1 		number;
   l_rejected_count_2 		number;
   l_err_msg_1				varchar2(2000);
   l_err_msg_2				varchar2(2000);
   l_err_msg_tmp_1			varchar2(2000); 
   l_err_msg_tmp_2			varchar2(2000); 
   
   l_gl_default_id			number;
                    		                 		
   cursor cur_account is
      select * from CRST_RA_ACCOUNT_DEFAULTS_ALL;
      
   cursor cur_segment(p_gl_default_id number) is
      select * from CRST_RA_ACCOUNT_DEFAULT_SEGMEN
      where gl_default_id = p_gl_default_id;   

begin
   l_processed_count_1 := 0;
   l_rejected_count_1  := 0;
   l_err_msg_1 := '';
   
   l_processed_count_2 := 0;
   l_rejected_count_2  := 0;
   l_err_msg_2 := '';
   
   for cur1 in cur_account loop   	    
   	 l_err_msg_tmp_1 := '';
   	 
   	 select RA_ACCOUNT_DEFAULTS_S.NEXTVAL
   	 into l_gl_default_id
   	 from dual;
   	 
   	 begin
       -- import data
       insert into RA_ACCOUNT_DEFAULTS_ALL 
       (
         GL_DEFAULT_ID       ,
         LAST_UPDATE_DATE    ,
         LAST_UPDATED_BY     ,
         CREATION_DATE       ,
         CREATED_BY          ,
         LAST_UPDATE_LOGIN   ,
         TYPE                ,
         ATTRIBUTE_CATEGORY  ,
         ATTRIBUTE1          ,
         ATTRIBUTE2          ,
         ATTRIBUTE3          ,
         ATTRIBUTE4          ,
         ATTRIBUTE5          ,
         ATTRIBUTE6          ,
         ATTRIBUTE7          ,
         ATTRIBUTE8          ,
         ATTRIBUTE9          ,
         ATTRIBUTE10         ,
         ATTRIBUTE11         ,
         ATTRIBUTE12         ,
         ATTRIBUTE13         ,
         ATTRIBUTE14         ,
         ATTRIBUTE15         ,
         ORG_ID
       )
       values
       (
         l_gl_default_id,
         cur1.LAST_UPDATE_DATE    ,
         CAPBRIMP_MAPPING.GetUserMapping(cur1.LAST_UPDATED_BY),
         cur1.CREATION_DATE       ,
         CAPBRIMP_MAPPING.GetUserMapping(cur1.CREATED_BY),
         cur1.LAST_UPDATE_LOGIN   ,
         cur1.TYPE                ,
         cur1.ATTRIBUTE_CATEGORY  ,
         cur1.ATTRIBUTE1          ,
         cur1.ATTRIBUTE2          ,
         cur1.ATTRIBUTE3          ,
         cur1.ATTRIBUTE4          ,
         cur1.ATTRIBUTE5          ,
         cur1.ATTRIBUTE6          ,
         cur1.ATTRIBUTE7          ,
         cur1.ATTRIBUTE8          ,
         cur1.ATTRIBUTE9          ,
         cur1.ATTRIBUTE10         ,
         cur1.ATTRIBUTE11         ,
         cur1.ATTRIBUTE12         ,
         cur1.ATTRIBUTE13         ,
         cur1.ATTRIBUTE14         ,
         cur1.ATTRIBUTE15         ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur1.ORG_ID)
       );  
     exception
       when others then
     	 l_err_msg_tmp_1 := sqlerrm;
     end;
     
     for cur2 in cur_segment(cur1.gl_default_id) loop 
       l_err_msg_tmp_2 := '';
       	  	 
       begin	  	       
         -- import data
         insert into CRST_RA_ACCOUNT_DEFAULT_SEGMEN 
         (
           GL_DEFAULT_SEGMENT_ID  ,
           LAST_UPDATE_DATE       ,
           LAST_UPDATED_BY        ,
           CREATION_DATE          ,
           CREATED_BY             ,
           LAST_UPDATE_LOGIN      ,
           GL_DEFAULT_ID          ,
           SEGMENT                ,
           SEGMENT_NUM            ,
           TABLE_NAME             ,
           CONSTANTS              ,
           ATTRIBUTE_CATEGORY     ,
           ATTRIBUTE1             ,
           ATTRIBUTE2             ,
           ATTRIBUTE3             ,
           ATTRIBUTE4             ,
           ATTRIBUTE5             ,
           ATTRIBUTE6             ,
           ATTRIBUTE7             ,
           ATTRIBUTE8             ,
           ATTRIBUTE9             ,
           ATTRIBUTE10            ,
           ATTRIBUTE11            ,
           ATTRIBUTE12            ,
           ATTRIBUTE13            ,
           ATTRIBUTE14            ,
           ATTRIBUTE15
         )
         values
         (
           RA_ACCOUNT_DEFAULT_SEGMENTS_S.NEXTVAL,
           cur2.LAST_UPDATE_DATE       ,
           CAPBRIMP_MAPPING.GetUserMapping(cur2.LAST_UPDATED_BY),
           cur2.CREATION_DATE          ,
           CAPBRIMP_MAPPING.GetUserMapping(cur2.CREATED_BY),
           cur2.LAST_UPDATE_LOGIN      ,
           l_gl_default_id ,
           cur2.SEGMENT                ,
           cur2.SEGMENT_NUM            ,
           cur2.TABLE_NAME             ,
           cur2.CONSTANTS              ,
           cur2.ATTRIBUTE_CATEGORY     ,
           cur2.ATTRIBUTE1             ,
           cur2.ATTRIBUTE2             ,
           cur2.ATTRIBUTE3             ,
           cur2.ATTRIBUTE4             ,
           cur2.ATTRIBUTE5             ,
           cur2.ATTRIBUTE6             ,
           cur2.ATTRIBUTE7             ,
           cur2.ATTRIBUTE8             ,
           cur2.ATTRIBUTE9             ,
           cur2.ATTRIBUTE10            ,
           cur2.ATTRIBUTE11            ,
           cur2.ATTRIBUTE12            ,
           cur2.ATTRIBUTE13            ,
           cur2.ATTRIBUTE14            ,
           cur2.ATTRIBUTE15
         );  
       exception
         when others then
     	   l_err_msg_tmp_2 := sqlerrm;
       end;
	   
	   if l_err_msg_tmp_2 = '' then
         l_processed_count_2 := l_processed_count_2 + 1;  
       else
         l_rejected_count_2 := l_rejected_count_2 + 1;
         l_err_msg_2 := l_err_msg_tmp_2; 
       end if;
     end loop;
     
     if l_err_msg_tmp_1 = '' then
       l_processed_count_1 := l_processed_count_1 + 1;  
     else
       l_rejected_count_1 := l_rejected_count_1 + 1;
       l_err_msg_1 := l_err_msg_tmp_1; 
     end if;
   end loop;
   
   if l_rejected_count_1 <> 0 and l_rejected_count_2 <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_AUTO_ACCOUNTING', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg_1 );
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_AUTO_ACCOUNTING_SEGMENT', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg_2 );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit